﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KeepAnimals.DAL
{
    public class Connection
    {
        public const string TABLE_NAME_ANIMALS = "animals";
        public const string TABLE_NAME_BOX = "box";
        public const string TABLE_NAME_DEPARTMENT = "departments";
        public const string TABLE_NAME_AREA = "area";


        private static MySqlConnection connection;
        private static string server;
        private static string database;
        private static string uid;
        private static string password;

        //Constructor
        private Connection()
        {
           // Initialize();

        }
        public static MySqlConnection getInstance()
        {
            if (connection == null) //Les locks prennent du temps, il est préférable de vérifier d'abord la nullité de l'instance.
            {

                server = "localhost";
                database = "keepanimals";
                uid = "root";
                password = "";
                string connectionString;
                connectionString = "SERVER=" + server + ";" + "DATABASE=" +
                database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

                connection = new MySqlConnection(connectionString);
                
            }

            return connection;
        }

        //Initialize values
        /*private static void Initialize()
        {
            server = "localhost";
            database = "keepanimals";
            uid = "root";
            password = "";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }*/

        // Fonction pour récupérér une commande, pour chaque object, on crée une commande sur lui meme et on la base a la base qui va inserer la requete
        public static  MySqlCommand getInstanceOfCommand()
        {
            //Initialize();
            connection = getInstance();
            return connection.CreateCommand();
        }


        //open connection to database
        public static bool OpenConnection()
        {
            try
            {
                //Initialize();
                getInstance();
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
               
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        //Close connection
        public static bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }


        /// <summary>
        /// Adds the animal in database.
        /// </summary>
        /// <param name="animal">The animal.</param>
        /// <returns></returns>
        public static void AddUpdateCommandInDatabase(MySqlCommand command)
        {
          
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
                
        }
        //Fonction pour supprimé un objet de la base 
        public static void DelInDatabase( string tableName, int id)
        {
            //Initialize();
            MySqlCommand command = connection.CreateCommand();
            //MySqlCommand command = Connection.connection.CreateCommand();
           
            command.CommandText = " DELETE FROM "+tableName+" WHERE Id = "+id+";";
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
        //Methode a appeller quand on veut faire un get all ou get by id
        public static MySqlDataReader getAllData(MySqlCommand command)
        {
            //Initialize();
            MySqlDataReader rdr;
            //connection.Open();
            rdr = command.ExecuteReader();
            //connection.Close();
            return rdr;
        }

        /// <summary>
        /// Returns the last identifier animal.
        /// </summary>
        /// <returns></returns>
        public static int ReturnLastId(string nameTable)
        {   
            MySqlCommand command = connection.CreateCommand();
            command.CommandText= "SELECT Id FROM "+nameTable+" WHERE id=LAST_INSERT_ID()";
            connection.Open();
            int id = Convert.ToInt32(command.ExecuteScalar());
            connection.Close();
            return id;
        }

      /*  public string Select_login(string login)
        {

            string query = string.Format("SELECT * FROM contact WHERE  login='{0}'",login);

            string list = "";



            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list=(string) dataReader["login"];
                  
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                //this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
       /* public string Select_mdp(string mdp)
        {

            string query = string.Format("SELECT * FROM contact WHERE  password='{0}'", mdp);

            string list = "";



            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list = (string)dataReader["password"];

                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                //this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }

        public List<string> Select_event(DateTime date)
        {
            
            string query = string.Format("SELECT * FROM evenement WHERE  date='{0}'", date.ToString("yyyy-MM-dd"));

           // string list = "";

            List<string> list = new List<string>();

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                   // list += (string)dataReader["description"];
                    list.Add((string)dataReader["description"]);
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                //this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        */
        }
    }  
    
